﻿ if object_id('fn_MapHTML_AssetXmissionStructure') is not null
DROP FUNCTION fn_MapHTML_AssetXmissionStructure

GO

CREATE FUNCTION fn_MapHTML_AssetXmissionStructure
(
	@StructureId int
)
RETURNS varchar(max)
AS
BEGIN
	declare @img xml
	set @img = '<img src="http://dev-poleposition.com/DEV/Icons/transparent_150.png" />'

	declare @html varchar(max)
	select @html = 
	(
		select

		 'table-bubble' as '@class'
		,'0' as '@cellspacing'
		,'0' as '@cellpadding'
		,'column-structure' as 'tr/td/@class'
		,@img as 'tr/td'

		,(select 

			-- structure info

			  'table-structure' as '@class'
			 ,'0' as '@cellspacing'
			 ,'0' as '@cellpadding'

			 ,(select 'caption-structure' as '@class', 'Xmission Structure' for xml path ('caption'), type)

			 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Record' as '*' for xml path ('td'), type)
			 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', Structures.Utility_Asset_XMissionStructureId as '*' for xml path ('td'), type) for xml path('tr'), type)

			 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Date' as '*' for xml path ('td'), type)
			 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', convert(varchar, Structures.Date, 101) as '*' for xml path ('td'), type) for xml path('tr'), type) for xml path('table'), type) as 'tr/td'
			
			-- structure images
 
			, (select 
					 'border: solid black 1px;' as 'img/@style'
					,'image-structure' as 'img/@class'
					,replace(SettingValue, '{0}', Images.ImageId) as 'img/@src'
				from (
					select
						 structures.Utility_Asset_XMissionStructureId as StructureId
						,images.Utility_Asset_XMissionStructures_ImageId as ImageId
					from
						tb_Utility_Asset_XMissionStructures structures
						left join tb_Utility_Asset_XMissionStructures_Images images
							on images.Utility_Asset_XMissionStructureFK = structures.Utility_Asset_XMissionStructureId 
				) as Images
				join tb_Settings on SettingId = 13
				where Images.StructureId = Structures.Utility_Asset_XMissionStructureId
				for xml path ('p'), type) as 'tr/td'

			-- pole info

			 ,(Select 
				 'column-pole' as '@class' 
				,@img as '*'

				,(select 
					  'table-pole' as '@class'
					 ,'0' as '@cellspacing'
					 ,'0' as '@cellpadding'

					 ,(select 'caption-pole' as '@class', 'Pole ' + convert(varchar, SubPoles.PoleRank) for xml path ('caption'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Record' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleId as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Map' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleMap as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Section' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleSection as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Top' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleTop as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Species' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleSpecies as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Class' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleClass as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Year' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleYear as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Number' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleNumber as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Length' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleLength as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Last Visit' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleLVisit as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Last Visitor' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleLVisitor as '*' for xml path ('td'), type) for xml path('tr'), type)

/*
					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Latitide' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', convert(varchar, SubPoles.PoleLatitide) as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Longitude' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', convert(varchar, SubPoles.PoleLongitude) as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Altitude' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', convert(varchar, SubPoles.PoleAltitude) as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Satellite Count' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleSatelliteCount as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Satellite Time' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', convert(varchar, SubPoles.PoleSatelliteTime) as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Solution Available' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleSolutionAvailable as '*' for xml path ('td'), type) for xml path('tr'), type)

					 ,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Pole AID' as '*' for xml path ('td'), type)
					 ,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', SubPoles.PoleAID as '*' for xml path ('td'), type) for xml path('tr'), type)

*/
				from
					v_Utility_Asset_XmissionStructuresWithPoles as SubPoles
				where
					SubPoles.PoleId = Poles.PoleId
				for xml path ('table'), type)

				-- pole images

				, (select 
					 'image-pole' as 'img/@class'
					,replace(SettingValue, '{0}', Images.Utility_Asset_XMissionStructures_ImageId) as 'img/@src'
					from tb_Utility_Asset_XMissionStructures_Poles as SubPoles
					join tb_Utility_Asset_XMissionStructures_Images as Images on SubPoles.Utility_Asset_XMissionStructures_PoleId = Images.Utility_Asset_XMissionStructures_PoleFK
					join tb_Settings on SettingId = 13
					where SubPoles.Utility_Asset_XMissionStructures_PoleId = Poles.PoleId
					for xml path ('p'), type)

				-- gps visit

				,(select 
					 'table-visit' as '@class'
					,'0' as '@cellspacing'
					,'0' as '@cellpadding'

					,(select 'caption-visit' as '@class', 'GPS Visit' as '*' for xml path ('caption'), type)

					,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Visit Date' as '*' for xml path ('td'), type)
					,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', convert(varchar, SubVisit.Date, 101) as '*' for xml path ('td'), type) for xml path('tr'), type)
				from 
					tb_Vendor_Visit_XMissionStructures as SubVisit
					join tb_Vendor_Visit_XMissionStructures_Poles as SubPole
						on SubPole.Vendor_Visit_XMissionStructureFK = SubVisit.Vendor_Visit_XMissionStructureId
						and SubPole.Vendor_Visit_XMissionStructures_PoleId = Poles.PoleId
				for xml path ('table'), type)

				-- inspection visit

				,(select 
					 'table-visit' as '@class'
					,'0' as '@cellspacing'
					,'0' as '@cellpadding'
					
					,(select 'caption-visit' as '@class', 'Inspection Visit' as '*' for xml path ('caption'), type)

					,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Visit Date' as '*' for xml path ('td'), type)
					,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', convert(varchar, SubVisit.Date, 101) as '*' for xml path ('td'), type) for xml path('tr'), type)

					,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Result' as '*' for xml path ('td'), type)
					,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', IsNull(SubResultType.Name, '{blank}') as '*' for xml path ('td'), type) for xml path('tr'), type)

/*
					,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Inspection Type' as '*' for xml path ('td'), type)
					,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', IsNull(SubInspectionVisit.Name, '{blank}') as '*' for xml path ('td'), type) for xml path('tr'), type)

					,(select (select 'nowrap' as '@nowrap', 'cell-name' as '@class', 'Electrical' as '*' for xml path ('td'), type)
					,(select 'nowrap' as '@nowrap', 'cell-value' as '@class', IsNull(case SubResults.bElectrical when 1 then 'Yes' else 'No' end, '{blank}') as '*' for xml path ('td'), type) for xml path('tr'), type)
*/
				from 
					tb_Vendor_InspVisit_XMissionStructures as SubVisit
					join tb_Vendor_InspVisit_XmissionStructures_Poles as SubPole
						on SubPole.Vendor_InspVisit_XMissionStructureFk = SubVisit.Vendor_InspVisit_XMissionStructureId
						and SubPole.Utility_Asset_XMissionStructures_PoleFk = Poles.PoleId
					left join tb_Vendor_InspVisit_XmissionStructures_Results as SubResults
						on SubResults.Vendor_InspVisit_XMissionStructure_PoleFk = SubPole.Vendor_InspVisit_XMissionStructure_PoleId
					left join tb_Vendor_InspVisit_XmissionStructures_Inspection_Type SubInspectionVisit
						on SubInspectionVisit.Vendor_InspVisit_XmissionStructures_Inspection_TypeId = SubVisit.XMissionStructures_TopTypeFk
					left join tb_Vendor_InspVisit_XmissionStructures_ResultTypes as SubResultType
						on SubResultType.Vendor_InspVisit_XmissionStructures_ResultTypeId = SubResults.Vendor_InspVisit_XmissionStructures_ResultTypeFk
				for xml path ('table'), type)

				-- todo: pole gps visits
				
				from v_Utility_Asset_XmissionStructuresWithPoles as Poles
				where Poles.StructureId = Structures.Utility_Asset_XMissionStructureId
				for xml path ('td'), type) as 'tr'

		from
			tb_Utility_Asset_XMissionStructures as Structures
		where
			Structures.Utility_Asset_XMissionStructureId = @StructureId
		for xml path ('table')
	)
	return @html
END
GO

select convert(xml, dbo.fn_MapHTML_AssetXmissionStructure(40))
